﻿<cfsilent>

	<cfflush interval="100" />
	
	<cfscript>

		temFile = GetTempDirectory() & createUUID() & ".xls";
		bookUpdate = 0;
		bookInsert = 0;
		
		mathAdvice = getProperty("serviceFactory").getBean("senateMathAdvice");
		strAdvice = getProperty("serviceFactory").getBean("stringAdvice");
		
		sequenceCourseBook = getProperty("serviceFactory").getBean("sequenceCourseBook");
		
		termId = event.getArg("TermID");

		targetArgs = structNew();
		structInsert(targetArgs, "TabID", "labImport", true);
		
	</cfscript>
</cfsilent>
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>导入教材数据</title>
<link href="<cfoutput>#getProperty('cdnServer')#</cfoutput><cfoutput>#getContextRoot()#</cfoutput>/acadmicRes/assets/css/impoter.css" rel="stylesheet" type="text/css">
</head>

<body>
	
	<cfif isDefined("FORM.upload")>
		
		<!--- 将文件保存至临时目录 --->
		<cftry>
			<cffile action="upload" fileField="upload" destination="#temFile#" nameconflict="overwrite">
			<cfcatch type="application">
				<p class="error">上传的文件类型不正确, 请使用电子表格作为数据导入文件.</p>
			</cfcatch>
		</cftry>
		
		<!--- 将文件转换为电子表格对象 --->
		<cfif fileExists(temFile)>
			<cftry>
				<cfspreadsheet action="read" src="#temFile#" excludeHeaderRow="true" headerrow="1" query="rs" sheetname="教材征订计划">
				<cfcatch type="application">
					<p class="error">电子表格文件格式不正确, 请使用下载的模板文件录入教材信息</p>
				</cfcatch>
			</cftry>
			
			<!--- 文件已成功转换为查询对象 --->
			<cfif isDefined("rs") and isQuery(rs)>

				<p class="success">尝试读取电子表格数据 ... 完成</p>

				<!--- 检查必要数据字段 --->	
				
				<cfset columnChecked = true />
				<cfif not listFindNoCase(rs.columnList, "学院")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "课程")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "ISBN")><cfset columnChecked = false /></cfif>
				
				<cfif not columnChecked>
					<p class="error">电子表格数据列不完整, 请检查模板文件是否包含以下必须字段信息: 学院, 课程, ISBN</p>
				</cfif>
				
				<cfif columnChecked>

					<p class="success">正在检查数据子项 ... 完成</p> 

					<cftry>
					
						<cftransaction>

							<cfloop query="rs">
							
								<cfset courseName = strAdvice.trimExcel( rs['课程'][rs.currentRow] ) />
								<cfset instituteName = strAdvice.trimExcel( rs['学院'][rs.currentRow] ) />
								
								<cfset isbn = strAdvice.trimExcel( rs['ISBN'][rs.currentRow] ) />
								<cfset isbn = replace( isbn, "-", "", "ALL") />
							
                            	
                            
								<!--- 检查 学院+课程名称 是否能找到匹配的记录 --->
                                <cfset sql = "	SELECT
													c.cid
												FROM
													t_course c INNER JOIN t_institute i ON i.institute_id = c.institute_id
												WHERE
													c.course_name = :courseName 
													AND
													i.institute_name = :instituteName " />
								
                                <cfset queryObj = new Query(datasource = application.dnsMaster)/>
								<cfset queryObj.addParam(name = "courseName", value = courseName, cfsqltype = "cf_sql_varchar")/>
                                <cfset queryObj.addParam(name = "instituteName", value = instituteName, cfsqltype = "cf_sql_varchar")/>
                                <cfset rs_courseCheck = queryObj.execute(sql = sql).getResult() />
                                
                                <cfif rs_courseCheck.recordCount eq 1>
										
										<!--- 检查 ISBN 号码 --->
                                        <cfset sql = "	SELECT
															b.bid,b.book_prise
														FROM
															t_book b
														WHERE
															b.book_isbn = :isbn 
														ORDER BY
															b.bid DESC" />

                                        <cfset queryObj = new Query(datasource = application.dnsMaster)/>
                                        <cfset queryObj.addParam(name = "isbn", value = isbn, cfsqltype = "cf_sql_varchar")/>
                                        <cfset rs_bookCheck = queryObj.execute(sql = sql).getResult() />
                                        
                                        <cfif rs_bookCheck.recordCount>
											
												<!--- 课程与教材都无误 --->
                                                <cfset sql = "	SELECT
																	cb.cbid,
																	cb.contract,
																	cb.telephone,
																	cb.charge,
																	cb.prise,
																	cb.discount,
																	cb.plan_student,
																	cb.plan_teacher
																FROM
																	t_course_book cb
																WHERE
																	cb.term_id = :termId 
																	AND cb.cid = :courseId 
																	AND cb.bid = :bookId " />
                                                
                                                <cfset queryObj = new Query(datasource = application.dnsMaster)/>
												<cfset queryObj.addParam(name = "termId", value = termId, cfsqltype = "cf_sql_char")/>
                                                <cfset queryObj.addParam(name = "bookId", value = rs_bookCheck.bid, cfsqltype = "cf_sql_varchar")/>
                                                <cfset queryObj.addParam(name = "courseId", value = rs_courseCheck.cid, cfsqltype = "cf_sql_varchar")/>
                                                <cfset rs_courseBook = queryObj.execute(sql = sql).getResult() />
												
												<cfif rs_courseBook.recordCount eq 0>
													
														<!--- 添加教材计划 --->
														<cfset bookInsert++ />
                                                        
														<cfset sql = "	INSERT INTO t_course_book(
																			cbid,
																			term_id,
																			cid,
																			bid,
																			prise,
																			discount,
																			charge,
																			plan_sum,
																			bidx,
																			plan_student,
																			plan_teacher,
																			contract,
																			telephone
																		)VALUES(
																			:courseBookId,
																			:termId,
																			:courseId,
																			:bookId,
																			:prise,
																			:disCount,
																			:charge,
																			:planSum,
																			:bookIndex,
																			:forStudent,
																			:forTeacher,
																			:contract,
																			:telephone 
																		)" />
                                                        
                                                        
                                                        <cfset sequenceCourseBook.setTermID(termId)/>
                                                        <cfset sequenceCourseBook.setCourseID(rs_courseCheck.cid)/>
                                                        <cfset bookIdx = sequenceCourseBook.getBookIDX()/>
                                                        <cfset sequenceCourseBook.setBookIndex(bookIdx)/>
                                                        <cfset courseBookId = sequenceCourseBook.getID()/>

                                                       	<cfset prise = rs_bookCheck.book_prise />
                                                        <cfset discount = 1 />
                                                        <cfset charge = rs_bookCheck.book_prise />
                                                        <cfset planSum = 0 />
                                                        <cfset forStudent = 0 />
                                                        <cfset forTeacher = 0 />
                                                        <cfset contract = "" />
                                                        <cfset telephone = "" >
                                                        
                                                    	<!--- 补充其他可选信息 --->
														<cfif isNumeric( rs['定价'][rs.currentRow] ) and rs['定价'][rs.currentRow] gt 0>
                                                            <cfset prise = rs['定价'][rs.currentRow] />
                                                            <cfset charge = rs['定价'][rs.currentRow] />
                                                        </cfif>
                                                        
                                                        <cfif isNumeric( rs['折扣率'][rs.currentRow] ) and rs['折扣率'][rs.currentRow] gt 0 and rs['折扣率'][rs.currentRow] lt 1>
                                                            <cfset discount = rs['折扣率'][rs.currentRow] />
                                                            <!--- 重新计算售价 --->
                                                            <cfset charge = numberFormat( rs_bookCheck.book_prise * rs['折扣率'][rs.currentRow] + 0.05, '_._') />
                                                        </cfif>
                                                        
                                                        <cfif isNumeric( rs['学生用量'][rs.currentRow] ) and rs['学生用量'][rs.currentRow] gt 0>
                                                            <cfset forStudent = rs['学生用量'][rs.currentRow] />
                                                        </cfif>
                                                        
                                                        <cfif isNumeric( rs['教师用量'][rs.currentRow] ) and rs['教师用量'][rs.currentRow] gt 0>
                                                            <cfset forTeacher = rs['教师用量'][rs.currentRow] />
                                                        </cfif>
                                                        
                                                        <!--- 刷新总用量 --->
                                                        <cfset planSum = forStudent + forTeacher />
                                                        
                                                        <cfset contract1 = strAdvice.trimExcel( rs['联系人'][rs.currentRow] ) />
                                                        <cfset telephone1 = strAdvice.trimExcel( rs['联系电话'][rs.currentRow] ) />
                                                        
                                                        <cfif len( contract1 )>
                                                            <cfset contract = contract1 />
                                                        </cfif>
        												
                                                        <cfif len( telephone1 )>
                                                            <cfset telephone = telephone1 />
                                                        </cfif>
                                                        
               											<cfset queryObj = new Query(datasource = application.dnsMaster)/>
                                                        <cfset queryObj.addParam(name = "courseBookId", value = courseBookId, cfsqltype = "cf_sql_varchar")/>
                                                        <cfset queryObj.addParam(name = "termId", value = termId, cfsqltype = "cf_sql_char")/>
                                                        <cfset queryObj.addParam(name = "courseId", value = rs_courseCheck.cid, cfsqltype = "cf_sql_varchar")/>
                                                        <cfset queryObj.addParam(name = "bookId", value = rs_bookCheck.bid, cfsqltype = "cf_sql_varchar")/>
                                                        <cfset queryObj.addParam(name = "prise", value = prise, cfsqltype = "cf_sql_decimal", scale = 2)/>
														<cfset queryObj.addParam(name = "disCount", value = discount, cfsqltype = "cf_sql_decimal", scale = 2)/>
                                                        <cfset queryObj.addParam(name = "charge", value = charge, cfsqltype = "cf_sql_decimal", scale = 2)/>
                                                        <cfset queryObj.addParam(name = "planSum", value = planSum, cfsqltype = "cf_sql_integer")/>
                                                        <cfset queryObj.addParam(name = "bookIndex", value = bookIdx, cfsqltype = "cf_sql_char")/>
														<cfset queryObj.addParam(name = "forStudent", value = forStudent, cfsqltype = "cf_sql_integer")/>
                                                        <cfset queryObj.addParam(name = "forTeacher", value = forTeacher, cfsqltype = "cf_sql_integer")/>
                                                        <cfset queryObj.addParam(name = "contract", value = contract, cfsqltype = "cf_sql_varchar")/>
                                                        <cfset queryObj.addParam(name = "telephone", value = telephone, cfsqltype = "cf_sql_varchar")/>
														<cfset queryObj.execute(sql = sql) />
                                                        
                                                        <cftransaction action="commit"/>
                                                        
													<cfelse>
														<!--- 更新教材计划 --->
														<cfset bookUpdate++ />
                                                        <cfset sql = "	UPDATE
																			t_course_book t
																		SET
																			t.prise = :prise,
																			t.charge = :charge,
																			t.discount = :disCount,
																			t.plan_sum = :planSum,
																			t.plan_student = :forStudent,
																			t.plan_teacher = :forTeacher,
																			t.contract = :contract,
																			t.telephone = :telephone 
																		WHERE
																			t.cbid = :courseBookId " />

                                                        <cfset contract = rs_courseBook.contract />
                                                        <cfset telephone = rs_courseBook.telephone />
                                                        <cfset prise = rs_courseBook.prise />
                                                        <cfset charge = rs_courseBook.charge />
                                                        <cfset discount = rs_courseBook.discount />
                                                        <cfset forStudent = rs_courseBook.plan_student />
                                                        <cfset forTeacher = rs_courseBook.plan_teacher />
                                                        
                                                        <!--- 补充其他可选信息 --->
														<cfif isNumeric( rs['定价'][rs.currentRow] ) and rs['定价'][rs.currentRow] gt 0>
                                                            <cfset prise = rs['定价'][rs.currentRow] />
                                                            <cfset charge = rs['定价'][rs.currentRow] />
                                                        </cfif>
                                                        
                                                        <cfif isNumeric( rs['折扣率'][rs.currentRow] ) and rs['折扣率'][rs.currentRow] gt 0 and rs['折扣率'][rs.currentRow] lt 1>
                                                            <cfset discount = rs['折扣率'][rs.currentRow] />
                                                            <!--- 重新计算售价 --->
                                                            <cfset charge = numberFormat( prise * rs['折扣率'][rs.currentRow] + 0.05, '_._') />
                                                        </cfif>
                                                        
                                                        <cfif isNumeric( rs['学生用量'][rs.currentRow] ) and rs['学生用量'][rs.currentRow] gt 0>
                                                            <cfset forStudent = rs['学生用量'][rs.currentRow] />
                                                        </cfif>
                                                        
                                                        <cfif isNumeric( rs['教师用量'][rs.currentRow] ) and rs['教师用量'][rs.currentRow] gt 0>
                                                            <cfset forTeacher = rs['教师用量'][rs.currentRow] />
                                                        </cfif>
                                                        
                                                        <!--- 刷新总用量 --->
                                                        <cfset planSum = forStudent + forTeacher />
                                                        
                                                        <cfset contract1 = strAdvice.trimExcel( rs['联系人'][rs.currentRow] ) />
                                                        <cfset telephone1 = strAdvice.trimExcel( rs['联系电话'][rs.currentRow] ) />
                                                        
                                                        <cfif len( contract1 )>
                                                            <cfset contract = contract1 />
                                                        </cfif>
        												
                                                        <cfif len( telephone1 )>
                                                            <cfset telephone = telephone1 />
                                                        </cfif>
                                                        
                                                        <cfset queryObj = new Query(datasource = application.dnsMaster)/>
                                                        <cfset queryObj.addParam(name = "courseBookId", value = rs_courseBook.cbid, cfsqltype = "cf_sql_varchar")/>
                                                        <cfset queryObj.addParam(name = "prise", value = prise, cfsqltype = "cf_sql_decimal", scale = 2)/>
														<cfset queryObj.addParam(name = "disCount", value = discount, cfsqltype = "cf_sql_decimal", scale = 2)/>
                                                        <cfset queryObj.addParam(name = "charge", value = charge, cfsqltype = "cf_sql_decimal", scale = 2)/>
                                                        <cfset queryObj.addParam(name = "planSum", value = planSum, cfsqltype = "cf_sql_integer")/>
														<cfset queryObj.addParam(name = "forStudent", value = forStudent, cfsqltype = "cf_sql_integer")/>
                                                        <cfset queryObj.addParam(name = "forTeacher", value = forTeacher, cfsqltype = "cf_sql_integer")/>
                                                        <cfset queryObj.addParam(name = "contract", value = contract, cfsqltype = "cf_sql_varchar")/>
                                                        <cfset queryObj.addParam(name = "telephone", value = telephone, cfsqltype = "cf_sql_varchar")/>
														<cfset queryObj.execute(sql = sql) />
                                                        
                                                        <cftransaction action="commit"/>
                                                        
												</cfif>

											<cfelse>
												<p class="error">ISBN编号 <cfoutput>#isbn#</cfoutput> 在系统中没有匹配记录</p>
										</cfif>
								
									<cfelse>
										<p class="error">课程 <cfoutput>#courseName#</cfoutput> 任课单位 <cfoutput>#instituteName#</cfoutput> 在系统中没有匹配记录</p>
								</cfif>
							</cfloop>
							
						</cftransaction>
						
						<cfcatch type="any">
							<p class="error">数据导入错误... <cfoutput>#cfcatch.Message#</cfoutput></p>
						</cfcatch>
					</cftry>

				</cfif>
				
			</cfif>
			
		</cfif>
		
		<p>新增教材数据 [<cfoutput>#bookInsert#</cfoutput>] 条, 更新现有教材数据 [<cfoutput>#bookUpdate#</cfoutput>] 条.</p>
		
		<a target="_parent" href="<cfoutput>#buildURL('bookPlan')#</cfoutput>">刷新教材计划数据</a>
		
		<!---<a target="_parent" href="<cfoutput>#buildURL('programCourse', targetArgs)#</cfoutput>">重新导入课程</a>--->
		
		<!--- 删除临时文件 --->
		<cfif fileExists(temFile)>
			<cffile action="delete" file="#temFile#" />		
		</cfif>
		
	</cfif>
	
</body>
</html>